<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of RepFacturaDetallada
 *
 * @author Bryan Cruz
 */
class RepEstadoCuotas extends ReportBase {
    
    public $cycle;
    public $career;

    function __construct() {
        $this->callbackSql = "cbSqlGenerador"; //funcion que genera sql
        $this->callbackRow = "funcRows"; //funcion para cada fila generada
        $this->paramPage = "rpt_estadoCuotas_param.php";
        $this->resultPage = "rpt_estadoCuotas_resultado.php";
        $this->rootPath = RPT_PATH + "include";
        $this->label = "Reporte Estado de Cuotas";
        $this->subtitle = $this->label;
    }

    /**
     * Genera el sql para el reporte
     */
    public function getCareer() {
        $this->career = array();
        $this->cycle = getParam("ciclo");
        if(getParam("career") != "all"){
            $this->career[0]['ID'] = getParam("career");
        }else{
            $i = 0;
            $career = $this->db->query_select("cllg_career");
            while(count($career) > $i){
                $this->career[$i]['ID'] = $career[$i]["ID"];
                $i++;
            }
            $this->career[$i]['ID'] = -1;
        };
    }
    
    public function generateSQL() {
        $this->getCareer();
        #$sqlRs = $this->db->queryToArray($this->lastQuery);
        #$this->lastResult = $sqlRs;
    }
    
    public function getYears($cycle){
        $queryYear = $this->db->queryToArray(sprintf("SELECT EXTRACT(YEAR FROM DATE_BEGIN) ANIO
                                 FROM cllg_college_cycle
                                 WHERE CODE='%s'", $cycle));
        $year = array();
        $year[0] = intval($queryYear[0]['ANIO']);
        $year[1] = $year[0] + 1;
        $year[2] = $year[0] + 2;
        
        return $year;
    }
    
    public function getMonths(){
        $arrMeses = array();
        $arrMeses[1]['mes'] = '1';
        $arrMeses[1]['tri'] = '1';
        $arrMeses[1]['mest'] = 'Enero';
        $arrMeses[2]['mes'] = '2';
        $arrMeses[2]['tri'] = '1';
        $arrMeses[2]['mest'] = 'Febrero';
        $arrMeses[3]['mes'] = '3';
        $arrMeses[3]['tri'] = '1';
        $arrMeses[3]['mest'] = 'Marzo';
        $arrMeses[4]['mes'] = '4';
        $arrMeses[4]['tri'] = '2';
        $arrMeses[4]['mest'] = 'Abril';
        $arrMeses[5]['mes'] = '5';
        $arrMeses[5]['tri'] = '2';
        $arrMeses[5]['mest'] = 'Mayo';
        $arrMeses[6]['mes'] = '6';
        $arrMeses[6]['tri'] = '2';
        $arrMeses[6]['mest'] = 'Junio';
        $arrMeses[7]['mes'] = '7';
        $arrMeses[7]['tri'] = '3';
        $arrMeses[7]['mest'] = 'Julio';
        $arrMeses[8]['mes'] = '8';
        $arrMeses[8]['tri'] = '3';
        $arrMeses[8]['mest'] = 'Agosto';
        $arrMeses[9]['mes'] = '9';
        $arrMeses[9]['tri'] = '3';
        $arrMeses[9]['mest'] = 'Septiembre';
        $arrMeses[10]['mes'] = '10';
        $arrMeses[10]['tri'] = '4';
        $arrMeses[10]['mest'] = 'Octubre';
        $arrMeses[11]['mes'] = '11';
        $arrMeses[11]['tri'] = '4';
        $arrMeses[11]['mest'] = 'Noviembre';
        $arrMeses[12]['mes'] = '12';
        $arrMeses[12]['tri'] = '4';
        $arrMeses[12]['mest'] = 'Diciembre';
        
        return $arrMeses;
    }
    
    public function getStudents($career, $cycle, $status = null){
        $cycleInf = $this->db->query_select("cllg_college_cycle", "CODE={$cycle}");
        $fechaInicioCiclo = $cycleInf[0]['DATE_BEGIN'];
        $fechaFinCiclo = $cycleInf[0]['DATE_END'];
        $sqlInact = "SELECT cc.FK_STUDENT 
                    FROM cllg_cargo c,cllg_account cc
                    WHERE NOT ISNULL(c.DATE_PAYED) 
                    AND c.DATE_LIMIT 
                    BETWEEN $fechaInicioCiclo 
                    AND $fechaFinCiclo 
                    AND c.FK_ACCOUNT = cc.ID 
                    AND cc.FK_STUDENT NOT IN (
                        SELECT FK_STUDENT
                        FROM cllg_student_assign b
                        WHERE b.FK_CYCLE IN ($cycle))
                GROUP BY cc.FK_STUDENT";
        
        $sql = sprintf("SELECT * 
                FROM cllg_student
                WHERE ID IN(
                    SELECT FK_STUDENT
                    FROM cllg_student_assign
                    WHERE FK_CAREER='%s'
                    AND FK_CYCLE='%s')", $career, $cycle);
        $stud = $this->db->queryToArray($status != null ? $sqlInact : $sql);        
        return $stud;
    }
    
    public function getStatus($stud, $career, $cycle){
        $sqlStat = "SELECT * 
                        FROM cllg_student_assign b  
                        WHERE b.FK_CYCLE IN ('{$cycle}') 
                        AND b.FK_CAREER='{$career}' 
                        AND STATUS=1 
                        AND FK_STUDENT='{$stud}'";
                        
         $stat = $this->db->queryToArray($sqlStat);
         if(count($stat) > 0){
             return true;
         }else{
             return false;
         }
    }
    
    public function getCargo($cycle, $career, $stud, $month, $year){
        $like = "%nscrip%";
        $sql = sprintf("SELECT *
                        FROM cllg_cargo 
                        WHERE FK_STUDENT_ASSIGN IN (
                          SELECT ID
                          FROM cllg_student_assign
                          WHERE FK_STUDENT='%s'
                          AND FK_CAREER='%s'
                          AND FK_CYCLE='%s'
                          AND CREATED=(
                              SELECT MAX(CREATED)
                              FROM cllg_student_assign
                              WHERE FK_STUDENT='%s'
                              AND FK_CAREER='%s'
                              AND FK_CYCLE='%s'))
                          AND NOT ISNULL(TOTAL)      
                          AND DESCRIPTION NOT like '%s'    
                          AND EXTRACT(MONTH FROM DATE_LIMIT)=%s
                          AND EXTRACT(YEAR FROM DATE_LIMIT)=%s
                          AND ID IN (
                            SELECT DISTINCT FK_CARGO
                            FROM cllg_mov_cargo)", $stud, $career, $cycle, $stud, $career, $cycle, $like, $month, $year);
                                 
        $cargo = $this->db->queryToArray($sql);
        return $cargo;
    }
    
    public function getFacturas($cargo){
        $in = "";
        foreach ($cargo as $c){
            $in = isEmpty($in) ? $in : "$in,";
            $in.= $c['ID'];
        }
        $sql = sprintf("SELECT DISTINCT FK_FACTURA
                        FROM cllg_mov_cargo
                        WHERE FK_CARGO IN (%s)
                        AND FK_FACTURA NOT IN (
                            SELECT ID
                            FROM cllg_factura
                            WHERE STATUS = 0)", $in);
        #$facturas = count($this->db->queryToArray($sql)) != 0 ? $this->db->queryToArray($sql) : 0;
        $facturas = count($this->db->queryToArray($sql)) > 0 ? $this->db->queryToArray($sql) : 0;
        return $facturas;
    }
    
    public function getFactura($id){
        $sql = sprintf("SELECT *
                        FROM cllg_factura
                        WHERE ID = '%s'
                        AND STATUS != 0", $id);
        $result = $this->db->queryToArray($sql);
        $factura = count($result) > 0 ? $result : 0;
        return $factura;
    }
    
    public function getMontoPagado($cargos){
        $total = 0;
        foreach ($cargos as $c){
            $sql = sprintf("SELECT SUM(MONTO) AS PAY
                            FROM cllg_mov_cargo
                            WHERE FK_CARGO='%s'
                            AND FK_FACTURA NOT IN(
                                SELECT ID
                                FROM cllg_factura
                                WHERE STATUS = 0)", $c['ID']);
            /*$sql = "SELECT SUM(stotal) PAY 
                    FROM(
                        SELECT SUM(AMOUNT - TOTAL_PENDIENTE) AS stotal
                        FROM cllg_cargo
                        WHERE TOTAL != 0
                        AND TOTAL_PENDIENTE != 0
                        AND ID = {$c['ID']}
                        UNION
                        SELECT SUM(AMOUNT) AS stotal
                        FROM cllg_cargo
                        WHERE TOTAL != 0
                        AND TOTAL_PENDIENTE = 0
                        AND ID = {$c['ID']}) TOTALES";*/
            $payed = $this->db->queryToArray($sql);
            $total += $payed[0]['PAY'];
        }
        return $total;
    }
    
    public function getIns($cycle, $career, $stud, $year){
        $like = '%nscrip%';
        $sql = sprintf("SELECT *
                        FROM cllg_cargo 
                        WHERE FK_STUDENT_ASSIGN IN (
                          SELECT ID
                          FROM cllg_student_assign
                          WHERE FK_STUDENT='%s'
                          AND FK_CAREER='%s'
                          AND FK_CYCLE='%s'
                          AND CREATED=(
                              SELECT MAX(CREATED)
                              FROM cllg_student_assign
                              WHERE FK_STUDENT='%s'
                              AND FK_CAREER='%s'
                              AND FK_CYCLE='%s'))
                         AND NOT ISNULL(TOTAL)     
                         AND DESCRIPTION like '%s'
                         AND EXTRACT(YEAR FROM DATE_LIMIT)=%s
                         AND ID IN (
                            SELECT DISTINCT FK_CARGO
                            FROM cllg_mov_cargo)", $stud, $career, $cycle, $stud, $career, $cycle, $like, $year);
        $cargo = $this->db->queryToArray($sql);
        return $cargo;
    }
    
    public function getNit($stud){
        $sql = sprintf("SELECT VALUE 
                        FROM cllg_table_valuebyfield
                        WHERE FK_TABLEFIELD = 'NIT'
                        AND PK_RECORD ='%s'", $stud);
        $nit = $this->db->queryToArray($sql);
        return ($nit[0]['VALUE'] == null ? "C/F" : $nit[0]['VALUE']); 
    }
    
    public function getPendiente($id){
        $sql = "SELECT  STUD, sum(stotal) TOTAL FROM(
                        SELECT  cllg_cargo.FK_ACCOUNT AS STUD,
                                SUM(cllg_cargo.AMOUNT) AS stotal
                        FROM    cllg_cargo,
                                cllg_account cc
                        WHERE   cc.ID = cllg_cargo.FK_ACCOUNT 
                        AND     cc.FK_STUDENT=$id
                        AND     ISNULL(cllg_cargo.DATE_PAYED)
                        AND     ISNULL(cllg_cargo.TOTAL_PENDIENTE)
                        GROUP   BY STUD
                        UNION
                        SELECT  cllg_cargo.FK_ACCOUNT AS STUD,
                                SUM(cllg_cargo.TOTAL_PENDIENTE) AS stotal
                        FROM    cllg_cargo,
                                cllg_account cc
                        WHERE   cc.ID = cllg_cargo.FK_ACCOUNT
                        AND     cc.FK_STUDENT = $id 
                        AND     cllg_cargo.TOTAL_PENDIENTE > 0
                        GROUP   BY STUD
                        UNION
                        SELECT  cllg_cargo.FK_ACCOUNT AS STUD,
                                SUM(cllg_cargo.PENALTY) AS stotal
                        FROM    cllg_cargo,
                                cllg_account cc
                        WHERE   cc.ID = cllg_cargo.FK_ACCOUNT 
                        AND     cc.FK_STUDENT=$id
                        AND     ISNULL(cllg_cargo.DATE_PAYED)
                        AND     cllg_cargo.DATE_LIMIT < NOW()
                        AND     ISNULL(cllg_cargo.TOTAL_PENDIENTE)
                        GROUP   BY STUD
                        ORDER   BY  STUD) DEUDAS GROUP BY STUD";
        $pendiente = $this->db->queryToArray($sql);
        return $pendiente[0]['TOTAL'];
    }
}

?>
